﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports System.IO

Public Class Auto_Order

    Private dataAdapter As New OleDbDataAdapter

    Private Sub Auto_Order_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.Auto_Order_Size = Me.Size
        My.Settings.Auto_Order_Pos = Me.Location
        GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\AutoOrderGW01.xml")
    End Sub

    Private Sub Auto_Order_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Size = My.Settings.Auto_Order_Size
        FormPos(My.Settings.Auto_Order_Pos)
        Me.Location = My.Settings.Auto_Order_Pos

        If File.Exists(My.Settings.ConfigPath & "\AutoOrderGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\AutoOrderGW01.xml")

    End Sub

    Function GetData(ByVal selectCommand As String)

        Try

            Dim ConCM As OleDb.OleDbConnection
            ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
            ConCM.Open()

            Dim cmd As New OleDbCommand(selectCommand, ConCM)

            Me.dataAdapter = New OleDb.OleDbDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.BindingSource1.DataSource = table


            ' Resize the GridView columns to fit the newly loaded content.
            Me.GridView1.BestFitColumns()

        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function

    Private Sub BarButtonItem1_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick
        Dim q As String
        q = ControlChars.Quote
        Me.TextBox1.Text = Me.TextBox1.Text.ToUpper
        Application.DoEvents()

        GetData("select MBWHLO as Stock , MBITNO, MMITDS as Description, MBSUWH as fromStock, MBREOP as ""MBREOP: ReorderPoint"", MBOPLC as ""MBOPLC: Planning Method"", MBSTQT ""MBSTQT: Bestand"", " & _
             "MBORQT , MBREQT , MBSSQT , MBPUIT  " & _
             "from Mitbal join mitmas on mbitno = mmitno " & _
             "where MBWHLO >= '" & Me.TextBox1.Text.ToString.ToString & "' and MBWHLO <= '" & Me.TextBox1.Text.ToString.ToString & "' and (MBORQT <> 0 or MBREQT <>0)" & _
             "order by MBWHLO, MBORQT DESC, MBITNO ")


    End Sub

    Private Sub TextBox1_KeyPress1(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
            Dim q As String
            q = ControlChars.Quote
            Me.TextBox1.Text = Me.TextBox1.Text.ToUpper
            Application.DoEvents()

            GetData("select MBWHLO as Stock , MBITNO, MMITDS as Description, MBSUWH as fromStock, MBREOP as ""MBREOP: ReorderPoint"", MBOPLC as ""MBOPLC: Planning Method"", MBSTQT ""MBSTQT: Bestand"", " & _
                  "MBORQT , MBREQT , MBSSQT , MBPUIT  " & _
                  "from Mitbal join mitmas on mbitno = mmitno " & _
                  "where MBWHLO >= '" & Me.TextBox1.Text.ToString & "' and MBWHLO <= '" & Me.TextBox1.Text.ToString.ToString & "' and (MBORQT <> 0 or MBREQT <>0)" & _
                  "order by MBWHLO, MBORQT DESC, MBITNO ")
        End If

        If File.Exists(My.Settings.ConfigPath & "\AutoOrderGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\AutoOrderGW01.xml")

    End Sub

    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_Auto_Order_Data.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub
End Class